package com.gxwzxy.medic.dao.Impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.gxwzxy.medic.dao.FamilyDao;
import com.gxwzxy.medic.entity.Family;
import com.gxwzxy.medic.until.Connect;

public class FamilyDaoImpl implements FamilyDao {
	
	//声明连接数据库的变量
	Connection conn = null;
	ResultSet rs = null;
	PreparedStatement ps = null;

	/**
	 * 查询家庭档案总数
	 */
	@Override
	public int findFamilySize() {
		String sql = "select COUNT(*) from family";
		int i = 0;
		try {
			//调用静态方法
			rs = Connect.executeF(sql);
			if(rs.next()) {
				i = rs.getInt(1);
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			Connect.close(conn, ps, rs);
		}
		return i;
	}

	/**
	 * 分页查询，start是开始的页数，size是一页行有多少数据
	 */
	@Override
	public List<Family> findFamilyIndex(int start, int size) {
		// 声明变量
		ArrayList<Family> af = new ArrayList<>();
		String sql = null;
		try {
			// 创建Sql语句
			sql = "select * from t_family limit ?,?";
			// 执行sql语句
			rs =Connect.executeF(sql, start,size);
			// 获取结果
			while (rs.next()) {
				Family f = new Family(rs.getString(1), rs.getString(2), rs.getString(3),rs.getString(4),rs.getString(5),
						rs.getString(6),rs.getString(7),rs.getInt(8),rs.getInt(9),rs.getString(10),rs.getString(11),rs.getString(12));
				af.add(f);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			Connect.close(conn, ps, rs);
		}
		// 关闭连接
		return af;
	}

	/**
	 * 添加家庭档案
	 */
	@Override
	public int addFamily(Family family) {
		String sql = "insert into t_family(xjbh,xzbh,cbh,zbh,jtbh,hsx,hzxm,jtrks,nyrks,jtdz,cjdasj,djy) values(?,?,?,?,?,?,?,?,?,?,?,?)";
		return Connect.executeDML(sql, family.getXjbh(),family.getXzbh(),family.getCbh(),family.getZbh(),family.getJtbh(),family.getHsx(),family.getHzxm(),family.getJtrks(),family.getNyrks(),family.getJtdz(),family.getCjdasj(),family.getDjy());
	}

	/**
	 * 删除家庭档案
	 */
	@Override
	public int delete(String jtbh) {
		PreparedStatement ps1 = null;
		int i =0;
		int j =0;
		try {
			conn = Connect.getConnection();
			String sql1 = "delete from t_family where jtbh=?";
			String sql2 = "delete from t_familyhold where jtbh=?";
			conn.setAutoCommit(false);
			ps = (PreparedStatement) conn.prepareStatement(sql2);
			ps1 = (PreparedStatement) conn.prepareStatement(sql1);
			ps.setString(1, jtbh);
			ps1.setString(1, jtbh);
			j = ps.executeUpdate();
			i = ps1.executeUpdate();
			conn.commit();
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}finally {
			Connect.closedouble(conn, ps);;
			Connect.closedouble(conn, ps1);
			try {
				ps.close();
			}catch(Exception e1) {
				e1.printStackTrace();
			}
		}
		if(i>0&&j>0 || i>0) {
			return 1;
		}else {
			return -1;
		}
	}

	/**
	 * 根据家庭编号查询家庭档案
	 */
	@Override
	public Family findByFamily(String jtbh) {
		Family family = null;
		String sql = null;
		try {
			conn = Connect.getConnection();
			sql = "select * from t_family where jtbh = ?";
			rs = Connect.executeF(sql, jtbh);
			if(rs.next()) {
				family = new Family();
				family.setXjbh(rs.getString("xjbh"));
				family.setXzbh(rs.getString("xzbh"));
				family.setCbh(rs.getString("cbh"));
				family.setZbh(rs.getString("zbh"));
				family.setJtbh(rs.getString("jtbh"));
				family.setHsx(rs.getString("hsx"));
				family.setHzxm(rs.getString("hzxm"));
				family.setJtrks(rs.getInt("jtrks"));
				family.setNyrks(rs.getInt("nyrks"));
				family.setJtdz(rs.getString("jtdz"));
				family.setCjdasj(rs.getString("cjdasj"));
				family.setDjy(rs.getString("djy"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			Connect.close(conn, ps, rs);
		}
		return family;
	}

	/**
	 * 修改家庭档案信息
	 */
	@Override
	public int update(Family family) {
		String sql = "update t_family set xjbh=?,xzbh=?,cbh=?,zbh=?,hsx=?,hzxm=?,jtrks=?,nyrks=?,jtdz=?,cjdasj=?,djy=? where jtbh=? ";
		return Connect.executeDML(sql, family.getXjbh(),family.getXzbh(),family.getCbh(),family.getZbh(),family.getHsx(),family.getHzxm(),family.getJtrks(),family.getNyrks(),family.getJtdz(),family.getCjdasj(),family.getDjy(),family.getJtbh());
	}

}
